package org.njo.webapp.root.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.njo.webapp.root.utility.ObjectNotExistsException;


public class AppPropertiesDAO {

    
    /**
     * 数据库连接对象.
     */
    private Connection connection = null;

    /**
     * 构造函数.<br>
     * 需要一个数据库连接对象作为参数.
     * 
     * @param argConnection
     *            数据库连接对象
     */
    public AppPropertiesDAO(Connection argConnection) {
        connection = argConnection;
    }
    
    
    /**
     * 从APP_PROPERTIES表,读取全部数据.
     * 
     * @param argStartIndex
     * @param argEndIndex
     * @return
     * @throws java.sql.SQLException
     */
    public List selectAllRecords(int argStartIndex, int argEndIndex)
            throws SQLException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        List resultList = new ArrayList();
        int curIndex = 0;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT PROP_KEY , ");
            selectSql.append("        DESCRIPTION, ");
            selectSql.append("        PROP_VALUE ");
            selectSql.append(" FROM APP_PROPERTIES ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            resultSet = selectStatement.executeQuery();
            if (resultSet.absolute(argStartIndex)) {
                curIndex = argStartIndex;
                do {
                    String[] message = new String[3];
                    message[0] = resultSet.getString(1);
                    message[1] = resultSet.getString(2);
                    message[2] = resultSet.getString(3);
                    resultList.add(message);
                    curIndex++;
                } while (resultSet.next() && (curIndex <= argEndIndex));
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return resultList;
    }    
    
    /**
     * 向数据库表中插入一条记录.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int insertProperty(String argPropertyKey, String argPropertyValue, String argPropertyDescription)
            throws SQLException {
        StringBuffer insertSql = null;
        PreparedStatement insertStatement = null;
        try {
            insertSql = new StringBuffer();
            insertSql.append(" INSERT INTO APP_PROPERTIES ( ");
            insertSql.append(" PROP_KEY , ");
            insertSql.append(" PROP_VALUE , ");
            insertSql.append(" DESCRIPTION ) ");
            insertSql.append(" VALUES ( ");
            insertSql.append(" ? , ");
            insertSql.append(" ? , ");
            insertSql.append(" ? ) ");

            insertStatement = connection.prepareStatement(insertSql.toString());
            insertStatement.setString(1, argPropertyKey);
            insertStatement.setString(2, argPropertyValue);
            insertStatement.setString(3, argPropertyDescription);

            return insertStatement.executeUpdate();
        } finally {
            if (insertStatement != null) {
                insertStatement.close();
                insertStatement = null;
            }
        }
    }
    
    
    /**
     * 根据指定的PropertyKey,取得数据.
     * 
     * @param argMessageKey
     * @return
     * @throws SQLException
     * @throws ObjectNotExistsException
     */
    public String[] selectPropertyByKey(String argPropertyKey) throws SQLException,
            ObjectNotExistsException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        String[] propertyData = null;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT PROP_KEY , ");
            selectSql.append("        PROP_VALUE, ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM APP_PROPERTIES ");
            selectSql.append(" WHERE PROP_KEY = ? ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            selectStatement.setString(1, argPropertyKey);

            resultSet = selectStatement.executeQuery();

            if (resultSet.next()) {
                propertyData = new String[3];
                propertyData[0] = (resultSet.getString(1));
                propertyData[1] = (resultSet.getString(2));
                propertyData[2] = (resultSet.getString(3));
            } else {
                throw new ObjectNotExistsException("not found data in database.");
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return propertyData;
    }
    
    /**
     * 更新数据库表中一条记录.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int updateProperty(String argPropertyKey, String argPropertyValue, String argPropertyDescription)
            throws SQLException {
        StringBuffer updateSql = null;
        PreparedStatement updateStatement = null;
        try {
            updateSql = new StringBuffer();
            updateSql.append(" UPDATE APP_PROPERTIES SET ");
            updateSql.append(" PROP_VALUE = ?, ");
            updateSql.append(" DESCRIPTION = ? ");
            updateSql.append(" WHERE PROP_KEY = ? ");

            updateStatement = connection.prepareStatement(updateSql.toString());
            updateStatement.setString(1, argPropertyValue);
            updateStatement.setString(2, argPropertyDescription);
            updateStatement.setString(3, argPropertyKey);

            return updateStatement.executeUpdate();
        } finally {
            if (updateStatement != null) {
                updateStatement.close();
                updateStatement = null;
            }
        }

    }
    
    /**
     * 删除数据.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int deletePropertyByKey(String argPropertyKey)
            throws SQLException {
        StringBuffer deleteSql = null;
        PreparedStatement deleteStatement = null;
        try {
            deleteSql = new StringBuffer();
            deleteSql.append(" DELETE FROM APP_PROPERTIES ");
            deleteSql.append(" WHERE PROP_KEY = ? ");
            deleteStatement = connection.prepareStatement(deleteSql.toString());
            deleteStatement.setString(1, argPropertyKey);
            return deleteStatement.executeUpdate();
        }
        finally {
            if (deleteStatement != null) {
                deleteStatement.close();
                deleteStatement = null;
            }
        }
    }
    
    
}
